﻿using System;
using WorkOrder.Model;
using System.Collections;

namespace WorkOrder.Business
{
    public class Business_Table_Maintenance
    {
        DbHelper dbhelper = new DbHelper();

        public bool Insert(Model_Table_Maintenance model)
        {
            string sqlstr;
            string tmp1 = "insert into Table_Maintenance (maintenance_ID";
            string tmp2 = ") values (NEWID()";
            if (model.Maintenance_final_time.Year != 2)
            {
                tmp1 = tmp1 + ",maintenance_final_time";
                tmp2 = tmp2 + ",'" + model.Maintenance_final_time.ToString() + "'";
            }

            if (model.Maintenance_time.Year != 2)
            {
                tmp1 = tmp1 + ",maintenance_time";
                tmp2 = tmp2 + ",'" + model.Maintenance_time.ToString() + "'";
            }

            tmp1 = tmp1 + ",inte_unit_is_check";
            if (!model.Inte_unit_is_check)
                tmp2 = tmp2 + ",0";
            else
                tmp2 = tmp2 + ",1";

            tmp1 = tmp1 + ",is_first_pass";
            if (!model.Is_first_pass)
                tmp2 = tmp2 + ",0";
            else
                tmp2 = tmp2 + ",1";

            tmp1 = tmp1 + ",maintain_unit_is_email";
            if (!model.Maintain_unit_is_email)
                tmp2 = tmp2 + ",0";
            else
                tmp2 = tmp2 + ",1";

            if (!string.IsNullOrEmpty(model.Maintain_unit))
            {
                tmp1 = tmp1 + ",maintain_unit";
                tmp2 = tmp2 + ",'" + model.Maintain_unit + "'";
            }

            if (!string.IsNullOrEmpty(model.Maintenance_number))
            {
                tmp1 = tmp1 + ",maintenance_number";
                tmp2 = tmp2 + ",'" + model.Maintenance_number + "'";
            }

            if (!string.IsNullOrEmpty(model.Maintenance_status))
            {
                tmp1 = tmp1 + ",maintenance_status";
                tmp2 = tmp2 + ",'" + model.Maintenance_status + "'";
            }

            if (!string.IsNullOrEmpty(model.Area_id))
            {
                tmp1 = tmp1 + ",area_id";
                tmp2 = tmp2 + "," + model.Area_id;
            }

            if (!string.IsNullOrEmpty(model.Remark))
            {
                tmp1 = tmp1 + ",remark";
                tmp2 = tmp2 + ",'" + model.Remark + "'";
            }

            if (!string.IsNullOrEmpty(model.Station_name))
            {
                tmp1 = tmp1 + ",station_name";
                tmp2 = tmp2 + ",'" + model.Station_name + "'";
            }

            if (!string.IsNullOrEmpty(model.Inte_unit_id))
            {
                tmp1 = tmp1 + ",inte_unit_id";
                tmp2 = tmp2 + "," + model.Inte_unit_id;
            }

            if (!string.IsNullOrEmpty(model.Product_id))
            {
                tmp1 = tmp1 + ",product_id";
                tmp2 = tmp2 + "," + model.Product_id;
            }

            if (!string.IsNullOrEmpty(model.Town_id))
            {
                tmp1 = tmp1 + ",town_id";
                tmp2 = tmp2 + "," + model.Town_id;
            }

            if (!string.IsNullOrEmpty(model.Operator))
            {
                tmp1 = tmp1 + ",operator";
                tmp2 = tmp2 + "," + model.Operator;
            }
            sqlstr = tmp1 + tmp2 + ");";
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }

        public bool Update(Model_Table_Maintenance model)
        {
            string sqlstr;
            string tmp1 = "update Table_Maintenance set ";
            string tmp2 = " where maintenance_ID='" + model.Maintenance_ID + "'";
            if (!model.Inte_unit_is_check)
                tmp1 = tmp1 + "inte_unit_is_check=0,";
            else
                tmp1 = tmp1 + "inte_unit_is_check=1,";

            if (!model.Is_first_pass)
                tmp1 = tmp1 + "is_first_pass=0,";
            else
                tmp1 = tmp1 + "is_first_pass=1,";

            if (!model.Maintain_unit_is_email)
                tmp1 = tmp1 + "maintain_unit_is_email=0,";
            else
                tmp1 = tmp1 + "maintain_unit_is_email=1,";

            if (model.Maintenance_final_time.Year != 2)
                tmp1 = tmp1 + "maintenance_final_time='" + model.Maintenance_final_time.ToString() + "',";
            else
                tmp1 = tmp1 + "maintenance_final_time=NULL,";

            if (model.Maintenance_time.Year != 2)
                tmp1 = tmp1 + "maintenance_time='" + model.Maintenance_time.ToString() + "',";
            else
                tmp1 = tmp1 + "maintenance_time=NULL,";

            if (!string.IsNullOrEmpty(model.Maintain_unit))
                tmp1 = tmp1 + "maintain_unit='" + model.Maintain_unit + "',";
            else
                tmp1 = tmp1 + "maintain_unit=NULL,";

            if (!string.IsNullOrEmpty(model.Maintenance_number))
                tmp1 = tmp1 + "maintenance_number='" + model.Maintenance_number + "',";
            else
                tmp1 = tmp1 + "maintenance_number=NULL,";

            if (!string.IsNullOrEmpty(model.Maintenance_status))
                tmp1 = tmp1 + "maintenance_status='" + model.Maintenance_status + "',";
            else
                tmp1 = tmp1 + "maintenance_status=NULL,";

            if (!string.IsNullOrEmpty(model.Area_id))
                tmp1 = tmp1 + "area_id=" + model.Area_id + ",";
            else
                tmp1 = tmp1 + "area_id=NULL,";

            if (!string.IsNullOrEmpty(model.Remark))
                tmp1 = tmp1 + "remark='" + model.Remark + "',";
            else
                tmp1 = tmp1 + "remark=NULL,";

            if (!string.IsNullOrEmpty(model.Station_name))
                tmp1 = tmp1 + "station_name='" + model.Station_name + "',";
            else
                tmp1 = tmp1 + "station_name=NULL,";

            if (!string.IsNullOrEmpty(model.Inte_unit_id))
                tmp1 = tmp1 + "inte_unit_id=" + model.Inte_unit_id + ",";
            else
                tmp1 = tmp1 + "inte_unit_id=NULL,";

            if (!string.IsNullOrEmpty(model.Product_id))
                tmp1 = tmp1 + "product_id=" + model.Product_id + ",";
            else
                tmp1 = tmp1 + "product_id=NULL,";

            if (!string.IsNullOrEmpty(model.Town_id))
                tmp1 = tmp1 + "town_id=" + model.Town_id + ",";
            else
                tmp1 = tmp1 + "town_id=NULL,";

            if (!string.IsNullOrEmpty(model.Operator))
                tmp1 = tmp1 + "operator=" + model.Operator + ",";
            else
                tmp1 = tmp1 + "operator=NULL,";

            sqlstr = tmp1.Remove(tmp1.Length - 1, 1) + tmp2;
            if (dbhelper.ExecuteNoQuery(sqlstr) > 0)
                return true;
            else
                return false;
        }

        public System.Data.DataTable QueryMaintenance(string condition)
        {
            string sqlstr = "select A.*,convert(varchar(10),A.maintenance_final_time,120) as maintenance_final_time,convert(varchar(10),A.maintenance_time,120) as maintenance_time,B.area_name,C.town_name,D.inte_unit_name,E.product_name,F.remark as name_remark from Table_Maintenance A left join " +
                "Table_Area B on A.area_id=B.area_id left join Table_Town C on A.town_id=C.town_id left join Table_Integrated_unit D on A.inte_unit_id=D.inte_unit_id left join Table_Product E on A.product_id=E.product_id left join Table_User F " +
                 "on A.operator=F.[user_id] where " + condition;
            return dbhelper.Query("Table_Maintenance", sqlstr);
        }

        public Model_Table_Maintenance GetMaintenanceInfo(string id)
        {
            string sql = "select * from Table_Maintenance where maintenance_ID='" + id + "'";
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sql))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Maintenance tmp = new Model_Table_Maintenance();
                    if (rdr["maintenance_ID"] != DBNull.Value)
                        tmp.Maintenance_ID = rdr["maintenance_ID"].ToString();
                    else
                        tmp.Maintenance_ID = "";

                    if (rdr["station_name"] != DBNull.Value)
                        tmp.Station_name = rdr["station_name"].ToString();
                    else
                        tmp.Station_name = "";

                    if (rdr["product_id"] != DBNull.Value)
                        tmp.Product_id = rdr["product_id"].ToString();
                    else
                        tmp.Product_id = "";

                    if (rdr["area_id"] != DBNull.Value)
                        tmp.Area_id = rdr["area_id"].ToString();
                    else
                        tmp.Area_id = "";

                    if (rdr["town_id"] != DBNull.Value)
                        tmp.Town_id = rdr["town_id"].ToString();
                    else
                        tmp.Town_id = "";

                    if (rdr["maintain_unit"] != DBNull.Value)
                        tmp.Maintain_unit = rdr["maintain_unit"].ToString();
                    else
                        tmp.Maintain_unit = "";

                    if (rdr["maintenance_number"] != DBNull.Value)
                        tmp.Maintenance_number = rdr["maintenance_number"].ToString();
                    else
                        tmp.Maintenance_number = "";

                    if (rdr["maintenance_status"] != DBNull.Value)
                        tmp.Maintenance_status = rdr["maintenance_status"].ToString();
                    else
                        tmp.Maintenance_status = "";

                    if (rdr["remark"] != DBNull.Value)
                        tmp.Remark = rdr["remark"].ToString();
                    else
                        tmp.Remark = "";
                    
                    if (rdr["inte_unit_id"] != DBNull.Value)
                        tmp.Inte_unit_id = rdr["inte_unit_id"].ToString();
                    else
                        tmp.Inte_unit_id = "";

                    if (rdr["maintain_unit_is_email"] != DBNull.Value)
                        tmp.Maintain_unit_is_email = Convert.ToBoolean(rdr["maintain_unit_is_email"]);
                    else
                        tmp.Maintain_unit_is_email = false;

                    if (rdr["is_first_pass"] != DBNull.Value)
                        tmp.Is_first_pass = Convert.ToBoolean(rdr["is_first_pass"]);
                    else
                        tmp.Is_first_pass = false;

                    if (rdr["inte_unit_is_check"] != DBNull.Value)
                        tmp.Inte_unit_is_check = Convert.ToBoolean(rdr["inte_unit_is_check"]);
                    else
                        tmp.Inte_unit_is_check = false;

                    if (rdr["maintenance_time"] != DBNull.Value)
                        tmp.Maintenance_time = Convert.ToDateTime(rdr["maintenance_time"]);
                    else
                        tmp.Maintenance_time = new DateTime(2, 2, 2);

                    if (rdr["maintenance_final_time"] != DBNull.Value)
                        tmp.Maintenance_final_time = Convert.ToDateTime(rdr["maintenance_final_time"]);
                    else
                        tmp.Maintenance_final_time = new DateTime(2, 2, 2);

                    if (rdr["operator"] != DBNull.Value)
                        tmp.Operator = rdr["operator"].ToString();
                    else
                        tmp.Operator = "";

                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else return null;
            }
        }

        #region 返回集成单位资料
        public System.Data.DataSet GetIntegratedUnit()
        {
            string sqlstr = "select inte_unit_id,inte_unit_name from Table_Integrated_unit";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回产品类别资料
        public System.Data.DataSet GetProduct()
        {
            string sqlstr = "select product_id,product_name from Table_Product";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据id返回产品类别
        public Model_Table_Product GetProductByID(string id)
        {
            string sqlstr = "select * from Table_Product where product_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Product tmp = new Model_Table_Product(rdr["product_id"].ToString(), rdr["product_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 返回镇区资料
        public System.Data.DataSet GetTown()
        {
            string sqlstr = "select town_id,town_name from Table_Town";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 返回区域资料
        public System.Data.DataSet GetArea()
        {
            string sqlstr = "select area_id,area_name from Table_Area";
            return dbhelper.Query(sqlstr);
        }
        #endregion

        #region 根据条件返回区域
        public Model_Table_Area GetArea(string condition)
        {
            if (string.IsNullOrEmpty(condition)) condition = "1=1";
            string sqlstr = "select * from Table_Area where " + condition;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion

        #region 根据镇区返回区域
        public Model_Table_Area GetAreaByTown(string townid)
        {
            if (!string.IsNullOrEmpty(townid))
            {
                string sqlstr = "select * from Table_Area,Table_Town where Table_Area.area_id=Table_Town.area_id and Table_Town.town_id=" + townid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null && rdr.Read())
                    {
                        Model_Table_Area tmp = new Model_Table_Area(rdr["area_id"].ToString(), rdr["area_name"].ToString(), rdr["remark"].ToString());
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return tmp;
                    }
                    else
                        return new Model_Table_Area("", "", "");
                }
            }
            else return new Model_Table_Area("", "", "");
        }
        #endregion

        #region 根据区域返回镇区
        public ArrayList GetTownByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id,town_name from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            object[] tmp = { rdr["town_id"], rdr["town_name"] };
                            arr.Add(tmp);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据区域返回镇区ID
        public ArrayList GetTownIdByArea(string areaid)
        {
            if (!string.IsNullOrEmpty(areaid))
            {
                ArrayList arr = new ArrayList();
                string sqlstr = "select town_id from Table_Town where area_id=" + areaid;
                using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
                {
                    if (rdr != null)
                    {
                        while (rdr.Read())
                        {
                            arr.Add(rdr["town_id"]);
                        }
                        rdr.Close();
                        dbhelper.CloseDbConnection();
                        return arr;
                    }
                    else
                        return null;
                }
            }
            else return null;
        }
        #endregion

        #region 根据id返回镇区
        public Model_Table_Town GetTownByID(string id)
        {
            string sqlstr = "select * from Table_Town where town_id=" + id;
            using (System.Data.SqlClient.SqlDataReader rdr = dbhelper.Search(sqlstr))
            {
                if (rdr != null && rdr.Read())
                {
                    Model_Table_Town tmp = new Model_Table_Town(rdr["town_id"].ToString(), rdr["area_id"].ToString(), rdr["town_name"].ToString(), rdr["remark"].ToString());
                    rdr.Close();
                    dbhelper.CloseDbConnection();
                    return tmp;
                }
                else
                    return null;
            }
        }
        #endregion
    }
}
